﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using MySql.Data.MySqlClient;
using HRForm;
using System.Configuration;

namespace HRAssistant
{
    public partial class EmployeeDataImportForm : Form
    {
        private string filepath = "";
        private int type = 0;

        public EmployeeDataImportForm(int type)
        {
            InitializeComponent();
            this.type = type;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //if (openFileDialog1.ShowDialog() == DialogResult.OK)
            //{
            //    filepath = openFileDialog1.SafeFileName;
            //    System.IO.StreamReader sr = new System.IO.StreamReader(filepath);
            //    this.ActiveMdiChild.ActiveControl.Text = sr.ReadToEnd();
            //    sr.Close();
            //}
            openFileDialog1.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);  //定义打开的默认文件夹位置
            openFileDialog1.ShowDialog();          //显示打开文件的窗口
            filepath = openFileDialog1.FileName;               //获得选择的文件路径

            comboBox1.Text = filepath;
            //extendedName = Path.GetExtension(filepath);       //获得文件扩展名
            //fileName1 = Path.GetFileName(filepath);           //获得文件名
        }

        private void button2_Click(object sender, EventArgs e)
        {

            if (filepath != String.Empty)
            {
                DataSet ds = HRForm.ReadWriteExcel.LoadDataFromExcel(filepath, "sheet1");

                int rowCount=ds.Tables[0].Rows.Count;
                int colCount=ds.Tables[0].Columns.Count;
                string[] record =new string[colCount];    //存放一行数据
                if(rowCount==0)
                {
                    MessageBox.Show("导入数据记录为0，请添加记录");
                }
                else
                {
                    MessageBox.Show("要插入的数据记录为："+(rowCount));
                    int count = 0;
                    try
                    {
                        //循环插入数据到数据库
                        for(int i=0;i<rowCount;i++)      
                        {
                            for(int j=0;j<colCount;j++)
                            {
                                record[j]=ds.Tables[0].Rows[i][j].ToString();
                            }

                         count=count +  ExecuteNonQuery(record);
                         
                        }
                        MessageBox.Show("受影响的数据记录为：" + count);
                    }
                    catch (Exception err)
                    {
                        MessageBox.Show("导入数据库失败：" + err.Message);
                        return;
                    }
                }
            }
            else
            {
                MessageBox.Show("请选择要导入的文件！");
            }
            
        }

        private void button3_Click(object sender, EventArgs e)
        {


        }

        private void button3_Click_1(object sender, EventArgs e)
        {
             
            MySqlConnection mysqlcon;
            MySqlDataAdapter mysqldata = new MySqlDataAdapter();
            DataSet dataset = new DataSet();
            mysqlcon = new MySqlConnection("server=localhost; user id=root; password=root; database=orangehrm_mysql; pooling=false;");
            mysqldata.SelectCommand = new MySqlCommand();
            mysqldata.SelectCommand.Connection = mysqlcon;
            mysqldata.SelectCommand.CommandText = "sp_work_hour";
            mysqldata.SelectCommand.CommandType = CommandType.StoredProcedure;
            //设置参数，添加到数据库  
            //MySqlParameter startdate_parameter = new MySqlParameter("?startdate", MySqlDbType.DateTime);//mysql的存储过程参数是以?打头的！！！！  
            //startdate_parameter.Value = datet;
            //mysqldata.SelectCommand.Parameters.Add(startdate_parameter);
            ////+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++  
            //MySqlParameter enddate_parameter = new MySqlParameter("?enddate", MySqlDbType.DateTime);//mysql的存储过程参数是以?打头的！！！！  
            //enddate_parameter.Value = email;
            //mysqldata.SelectCommand.Parameters.Add(enddate_parameter);
            ////++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++  
            //MySqlParameter password_parameter = new MySqlParameter("?xpassword", MySqlDbType.VarChar, 100);//mysql的存储过程参数是以?打头的！！！！  
            //password_parameter.Value = password;
            //mysqldata.SelectCommand.Parameters.Add(password_parameter);
            ////++++++++++++++++++++++++++返回值++++++++++++++++++++++++++++++++++++++++++  
            //MySqlParameter id_parameter = new MySqlParameter("?xid", MySqlDbType.Int16, 15);//mysql的存储过程参数是以?打头的！！！！  
            //id_parameter.Direction = ParameterDirection.Output;
            //mysqldata.SelectCommand.Parameters.Add(id_parameter);
            ////+++++++++++++++++++++++++打开数据库，插入数据，返回id值+++++++++++++++++++  
            //try
            //{
            //    mysqldata.Fill(dataset, "yuan_user_cc");

            //    id = (Int16)id_parameter.Value;
            //    return id.ToString();
            //}
            //catch (MySqlException ex)
            //{
            //    MessageBox.Show(ex.Message);
            //    return String.Empty;
            //}   
  
        }


        public int ExecuteNonQuery(string[] record)
        {
            string connStr = ConfigurationManager.AppSettings["MySqlConnStr"];//MySQL 数据库连接字符串;
            MySqlConnection conn = new MySqlConnection(connStr);
            string sqlcommand = "";
            MySqlCommand cmd=null;

            if (type == 1)
            {
                sqlcommand = "insert into hs_hr_employee (emp_number,employee_id,emp_lastname,emp_firstname,emp_middle_name,emp_smoker,emp_birthday,nation_code,emp_gender,emp_marital_status,emp_status,job_title_code,eeo_cat_code,emp_street1,emp_street2,city_code,coun_code,provin_code,emp_zipcode,emp_hm_telephone,emp_mobile,emp_work_telephone,emp_work_email,joined_date,emp_oth_email) values(@emp_number,@employee_id,@emp_lastname,@emp_firstname,@emp_middle_name,@emp_smoker,@emp_birthday,@nation_code,@emp_gender,@emp_marital_status,@emp_status,@job_title_code,@eeo_cat_code,@emp_street1,@emp_street2,@city_code,@coun_code,@provin_code,@emp_zipcode,@emp_hm_telephone,@emp_mobile,@emp_work_telephone,@emp_work_email,@joined_date,@emp_oth_email)";
                MySqlParameter employee_id = new MySqlParameter("@employee_id", MySqlDbType.Int32);
                employee_id.Value = record[0];
                MySqlParameter punch_in_user_time = new MySqlParameter("@punch_in_user_time", MySqlDbType.DateTime);
                punch_in_user_time.Value = record[1];
                MySqlParameter punch_out_user_time = new MySqlParameter("@punch_out_user_time", MySqlDbType.DateTime);
                punch_out_user_time.Value = record[2];
                MySqlParameter state = new MySqlParameter("@state", MySqlDbType.VarChar, 500);
                state.Value = record[3];

                cmd = new MySqlCommand(sqlcommand, conn);
                cmd.Parameters.Add(employee_id);
                cmd.Parameters.Add(punch_in_user_time);
                cmd.Parameters.Add(punch_out_user_time);
                cmd.Parameters.Add(state);
                
            }
            else if (type == 2)
            {
            }
            else if (type == 3)
            {
                sqlcommand = "insert into ohrm_attendance_record (employee_id,punch_in_user_time,punch_out_user_time,state) values(@employee_id,@punch_in_user_time,@punch_out_user_time,@state)";
                MySqlParameter employee_id = new MySqlParameter("@employee_id", MySqlDbType.Int32);
                employee_id.Value = record[0];
                MySqlParameter punch_in_user_time = new MySqlParameter("@punch_in_user_time", MySqlDbType.DateTime);
                punch_in_user_time.Value = record[1];
                MySqlParameter punch_out_user_time = new MySqlParameter("@punch_out_user_time", MySqlDbType.DateTime);
                punch_out_user_time.Value = record[2];
                MySqlParameter state = new MySqlParameter("@state", MySqlDbType.VarChar, 500);
                state.Value = record[3];

                cmd = new MySqlCommand(sqlcommand, conn);
                cmd.Parameters.Add(employee_id);
                cmd.Parameters.Add(punch_in_user_time);
                cmd.Parameters.Add(punch_out_user_time);
                cmd.Parameters.Add(state);
            }
            
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (System.Exception e)
            {
                StreamWriter sw = new StreamWriter("C:\\myerror.txt", true, Encoding.Default);
                sw.Write(e.Message);
                sw.Close();
                //一旦发生错误程序就停止运行，等待用户发现
                Console.Read();
                return -1;
            }
            finally
            {
                conn.Close();
            }
            
        }


        public void ChooseType(int type)
        {
            
        }
    }
}
